﻿//-----------------------------------------------------------------------------------------//
//-     Writen by Raymond Honderdors
//-     2013-05-14
//-
//-     Code can be used as is, no warrenty
//-     https://solutionmanagementki.codeplex.com/
//-----------------------------------------------------------------------------------------//


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.AnalysisServices;
namespace SMK_SSAS
{
    /// <summary>
    /// AMO Server A Class based on AMO SERVER Class / Major Object
    /// </summary>
    public class AMO_Server
    {
        private const string ConnectionBreak = ";";
        private const string Connection_Provider = "Provider=";
        private const string Connection_Password = "Password=";
        private const string Connection_UserID = "User ID=";
        private const string Connection_PersistSecurityInfo = "Persist Security Info=";
        private const string Connection_DataSource = "Data Source=";
        private const string Connection_ApplicationName = "Application Name=";
        private const string Connection_WorkstationID = "Workstation ID=";
        private const string Connection_IntegratedSecurity = "Integrated Security=";

        private const string DMV_Connections = "";
        //private const string DMV_Sessions = "select * from $system.discover_sessions where SESSION_ID <>('@ID1') and SESSION_ID <>('@ID2')";
        private const string DMV_Select = "SELECT";
        private const string DMV_From = "FROM";
        private const string DMV_SessionTable = "$system.discover_sessions";
        private const string DMV_SESSION_ID = "SESSION_ID";
        private const string DMV_And = "AND";
        private const string DMV_Where = "WHERE";
        private const string DMV_SESSION_USER_NAME = "SESSION_USER_NAME";
        private const string DMV_SESSION_CONNECTION_ID = "SESSION_CONNECTION_ID";
        private const string DMV_SESSION_STATUS = "SESSION_STATUS";
        private const string DMV_Distinct = "DISTINCT";
        private const string DMV_SESSION_CURRENT_DATABASE = "SESSION_CURRENT_DATABASE";
        private const string DMV_SESSION_USED_MEMORY = "SESSION_USED_MEMORY";
        private const string DMV_SESSION_START_TIME = "SESSION_START_TIME";
        private const string DMV_SESSION_LAST_COMMAND_START_TIME = "SESSION_LAST_COMMAND_START_TIME";
        private const string DMV_SESSION_LAST_COMMAND = "SESSION_LAST_COMMAND";
        private const string DMV_SESSION_SPID = "SESSION_SPID";


        private Microsoft.AnalysisServices.Server amoServer = null;
        private Microsoft.AnalysisServices.AdomdClient.AdomdConnection adomdcon = null;
        /// <summary>
        /// AMO Connection Will connect to an SSAS Server using AMO
        /// </summary>
        /// <param name="ServerName"></param>
        public void AMOConnection(string ServerName)
        {
            string Provider = "MSOLAP";
            Boolean IS = true;
            Boolean PSI = false;
            string Applicationname = "SMK AMO";
            string Hostname = Environment.MachineName;
            string UserId = Environment.UserDomainName + "\\" + Environment.UserName;
            amoServer = new Microsoft.AnalysisServices.Server();
            adomdcon = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection();
            if (ServerName == ".")
            {
                ServerName = Environment.MachineName;
            }
            try
            {
                amoServer.Connect(ConnectionStringBuilder(Provider, IS, PSI, UserId, string.Empty, ServerName, string.Empty, Applicationname, Hostname));
            }
            catch (Microsoft.AnalysisServices.AmoException amoex)
            {
                throw new Exception("AMOConnection", amoex);
            }
            try
            {
                adomdcon.ConnectionString = ConnectionStringBuilder(Provider, IS, PSI, UserId, string.Empty, ServerName, string.Empty, Applicationname, Hostname);
                adomdcon.Open();
            }
            catch (Microsoft.AnalysisServices.AdomdClient.AdomdException adomdex)
            {
                throw new Exception("ADOMDConnection", adomdex);
            }

        }
        /// <summary>
        /// Property will return AMO Server Name
        /// </summary>
        /// <returns> Server Name OF AMO Object</returns>
        public string ServerName()
        {
            if (amoServer != null && amoServer.Connected)
            {
                return amoServer.Name;
            }
            else
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Microsoft.AnalysisServices.Server amoserver()
        {
            if (amoServer != null && amoServer.Connected)
            {

                return amoServer;

            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean Isconnected()
        {
            if (amoServer != null && amoServer.Connected)
            {

                return true;

            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string ServerInfo()
        {
            if (amoServer != null && amoServer.Connected)
            {
                if (amoServer.Databases.Count > 0)
                {
                    return amoserver().ProductName + " " + amoserver().Version.ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            else
            {
                return string.Empty;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean HasDataBases()
        {
            if (amoServer != null && amoServer.Connected)
            {
                if (amoServer.Databases.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean HasTraces()
        {
            if (amoServer != null && amoServer.Connected)
            {
                if (amoServer.Traces.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean HasRoles()
        {
            if (amoServer != null && amoServer.Connected)
            {
                if (amoServer.Roles.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean HasAssemblies()
        {
            if (amoServer != null && amoServer.Connected)
            {
                if (amoServer.Assemblies.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean HasSessions()
        {
            if (SessionCount() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Int32 SessionCount()
        {
            Microsoft.AnalysisServices.AdomdClient.AdomdDataReader lData = null;
            Int32 result = 0;
            //lData = Getadomddata(DMV_Sessions.Replace("@ID1",amoServer.SessionID).Replace("@ID2",adomdcon.SessionID));
            lData = Getadomddata(DMV_Select + " " + DMV_SESSION_ID + " " + DMV_From + " " + DMV_SessionTable + " " + DMV_Where + " " + DMV_SESSION_ID + "<>'" + adomdcon.SessionID + "' " + DMV_And + " " + DMV_SESSION_ID + "<>'" + amoServer.SessionID + "'");
            if (lData != null)
            {

                if (lData.RecordsAffected > 0)
                {
                    result = lData.RecordsAffected;

                }
                else if (lData.RecordsAffected < 0)
                {
                    Int32 counter = 0;
                    while (lData.Read())
                    {
                        counter += 1;
                    }
                    result = counter;
                }
                lData.Close();
            }
            return result;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sCommand"></param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private Microsoft.AnalysisServices.AdomdClient.AdomdDataReader Getadomddata(string sCommand)
        {
            try
            {
                Microsoft.AnalysisServices.AdomdClient.AdomdCommand adomdcommand = adomdcon.CreateCommand();
                adomdcommand.CommandText = sCommand;
                return adomdcommand.ExecuteReader();
            }
            catch (Microsoft.AnalysisServices.AdomdClient.AdomdException adomdex)
            {
                return null;
                throw new Exception("ADOMDConnection", adomdex);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public SortedList<string, AMO_Key> DataBases()
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                foreach (Microsoft.AnalysisServices.Database db in amoServer.Databases)
                {
                    AMO_Database amodb = null;
                    amodb = new AMO_Database();
                    amodb.SetAMODatabase(this, db.Name);
                    AMO_Key amok = new AMO_Key();

                    amok.ID = db.ID;
                    amok.Name = db.Name;
                    amok.LevelObject = amodb;
                    result.Add(amoServer.Name + "\\" + db.ID, amok);
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public SortedList<string, AMO_Key> Roles()
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                foreach (Microsoft.AnalysisServices.Role db in amoServer.Roles)
                {
                    AMO_Roles amodb = null;
                    amodb = new AMO_Roles();
                    amodb.SetAMORole(this, db.Name);
                    AMO_Key amok = new AMO_Key();
                    amok.ID = db.ID;
                    amok.Name = db.Name;
                    amok.LevelObject = amodb;
                    result.Add(amoServer.Name + "\\" + db.ID, amok);
                }
                return result;
            }
            else
            {
                return null;



            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public SortedList<string, AMO_Key> Traces()
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                foreach (Microsoft.AnalysisServices.Trace db in amoServer.Traces)
                {
                    AMO_Key amok = new AMO_Key();
                    AMO_Traces amot = new AMO_Traces();
                    amot.SetAMOTrace(this, db.Name);
                    amok.ID = db.ID;
                    amok.Name = db.Name;
                    amok.LevelObject = amot;
                    result.Add(amoServer.Name + "\\" + db.ID, amok);
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public SortedList<string, AMO_Key> Assemblies()
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                foreach (Microsoft.AnalysisServices.Assembly db in amoServer.Assemblies)
                {
                    AMO_Key amok = new AMO_Key();
                    amok.ID = db.ID;
                    amok.Name = db.Name;
                    amok.LevelObject = db;
                    result.Add(amoServer.Name + "\\" + db.ID, amok);
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public SortedList<string, AMO_Key> Users()
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                Microsoft.AnalysisServices.AdomdClient.AdomdDataReader lData = null;
                lData = Getadomddata(DMV_Select + " " + DMV_SESSION_USER_NAME + " " + DMV_From + " " + DMV_SessionTable + " " + DMV_Where + " " + DMV_SESSION_ID + "<>'" + adomdcon.SessionID + "' " + DMV_And + " " + DMV_SESSION_ID + "<>'" + amoServer.SessionID + "'");
                if (lData != null)
                {
                    while (lData.Read())
                    {
                        if (!result.ContainsKey(amoServer.Name + "\\" + lData.GetValue(0).ToString().Replace("\\", "/")))
                        {
                            AMO_Key amok = new AMO_Key();
                            amok.ID = lData.GetValue(0).ToString().Replace("\\", "/");
                            amok.Name = lData.GetValue(0).ToString().Replace("\\", "/");

                            result.Add(amoServer.Name + "\\" + lData.GetValue(0).ToString().Replace("\\", "/"), amok);
                        }
                    }
                    lData.Close();
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public SortedList<string, AMO_Key> Sessions(string username)
        {
            if (amoServer != null && amoServer.Connected)
            {
                SortedList<string, AMO_Key> result = new SortedList<string, AMO_Key>();
                Microsoft.AnalysisServices.AdomdClient.AdomdDataReader lData = null;
                string sqlcommand = string.Empty;
                sqlcommand = DMV_Select + " "
                    + DMV_Distinct
                    + " "
                    + DMV_SESSION_CONNECTION_ID
                    + ", " + DMV_SESSION_STATUS
                    + ", " + DMV_SESSION_CURRENT_DATABASE
                    + ", " + DMV_SESSION_USED_MEMORY
                    + ", " + DMV_SESSION_START_TIME
                    + ", " + DMV_SESSION_LAST_COMMAND_START_TIME
                    + ", " + DMV_SESSION_LAST_COMMAND
                    + ", " + DMV_SESSION_ID
                    + ", " + DMV_SESSION_USER_NAME
                    + ", " + DMV_SESSION_SPID
                    + " " + DMV_From + " " + DMV_SessionTable
                    + " " + DMV_Where + " "
                    + DMV_SESSION_ID + "<>'" + adomdcon.SessionID + "' "
                    + DMV_And + " "
                    + DMV_SESSION_ID + "<>'" + amoServer.SessionID + "' "
                    + DMV_And + " "
                    + DMV_SESSION_USER_NAME + " = '" + username + "'";
                lData = Getadomddata(sqlcommand);
                if (lData != null)
                {
                    while (lData.Read())
                    {
                        AMO_Key amok = new AMO_Key();
                        AMO_SessionData amosession = new AMO_SessionData();
                        amosession.SESSION_CONNECTION_ID = lData.GetValue(0).ToString();
                        amosession.SESSION_STATUS = (Int32)lData.GetValue(1);
                        if (lData.GetValue(2) != null) amosession.SESSION_CURRENT_DATABASE = lData.GetValue(2).ToString();
                        amosession.SESSION_USED_MEMORY = (Int32)lData.GetValue(3);
                        amosession.SESSION_START_TIME = (DateTime)lData.GetValue(4);
                        amosession.SESSION_LAST_COMMAND_START_TIME = (DateTime)lData.GetValue(5);
                        amosession.SESSION_LAST_COMMAND = lData.GetValue(6).ToString();
                        amosession.SESSION_ID = lData.GetValue(7).ToString();
                        amosession.SESSION_USER_NAME = lData.GetValue(8).ToString();
                        amosession.SESSION_SPID = lData.GetValue(9).ToString();
                        amosession.Query = sqlcommand;
                        amok.ID = amosession.SESSION_CONNECTION_ID;
                        amok.Name = amosession.SESSION_CONNECTION_ID;
                        amok.LevelObject = amosession;
                        if (amosession.SESSION_STATUS == 1)
                        {
                            amok.IsActive = true;
                        }
                        else
                        {
                            amok.IsActive = false;
                        }
                        result.Add(amoServer.Name + "\\" + amosession.SESSION_CONNECTION_ID, amok);
                    }
                    lData.Close();
                }
                return result;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ProviderName"></param>
        /// <param name="IS"></param>
        /// <param name="PSI"></param>
        /// <param name="UserID"></param>
        /// <param name="UserPassword"></param>
        /// <param name="DataSource"></param>
        /// <param name="ISType"></param>
        /// <param name="ApplicationName"></param>
        /// <param name="HostName"></param>
        /// <returns></returns>
        private string ConnectionStringBuilder(string ProviderName, Boolean IS, Boolean PSI, string UserID, string UserPassword, string DataSource, string ISType, string ApplicationName, string HostName)
        {
            //Provider=MSOLAP;Password=s0abZ70;Persist Security Info=True;User ID=P8P67MPRO1\raymond;Initial Catalog=aaaaa;Data Source=P8P67MPRO1;Application Name=AppName;Workstation ID=HostName
            //Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;User ID=P8P67MPRO1\raymond;Initial Catalog=aaaaa;Data Source=P8P67MPRO1;Application Name=AppName;Workstation ID=HostName
            string ResultString = string.Empty;
            ResultString = Connection_Provider + ProviderName + ConnectionBreak;
            ResultString = ResultString + Connection_DataSource + DataSource + ConnectionBreak;
            ResultString = ResultString + Connection_ApplicationName + ApplicationName + ConnectionBreak;
            ResultString = ResultString + Connection_WorkstationID + HostName + ConnectionBreak;
            if (PSI)
            {
                ResultString = ResultString + Connection_PersistSecurityInfo + "True" + ConnectionBreak;
            }
            else
            {
                ResultString = ResultString + Connection_PersistSecurityInfo + "False" + ConnectionBreak;
            }
            if (IS)
            {
                if (ISType != string.Empty)
                {
                    ResultString = ResultString + Connection_IntegratedSecurity + ISType + ConnectionBreak;
                }
                else
                {
                    ResultString = ResultString + Connection_IntegratedSecurity + "SSPI" + ConnectionBreak;
                }
            }
            else
            {
                if (UserID != string.Empty && UserPassword != string.Empty)
                {
                    ResultString = ResultString + Connection_UserID + UserID + ConnectionBreak;
                    ResultString = ResultString + Connection_Password + UserPassword + ConnectionBreak;
                }
            }
            return ResultString;
        }

    }
}
